﻿using Intex.Tools;
using INTEX.DataBase.CE;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.SQLite;
using System.Xml.Linq;
namespace WPFExcelReport
{
    public partial class SDTDBSYNData : BaseFunction
    {
        string root = @"C:\IntexReport\MINTEX";
        string dbtype = "SDF";
        MINTEXDB MINTEXDB;
        public string CADCHAR = " ", ITXCHAR = " ";
        DataRow dwIntexStd = null;
        public Dictionary<string, object> CADTable = new Dictionary<string, object>();
        private Convert2Intex Convert2Intex;
        public DataSet SetExt;
        INTEXDBC INTEXDBC;
        Mapper DBMapper = null;
        string dbcon = "Data Source=|DataDirectory|C:\\IntexReport\\MINTEX\\PhoneDB\\MINTEXDB\\mintexdb.sdf";
        public SDTDBSYNData()
            : base()
        {
        }
        public SDTDBSYNData(OracleConnection _OracleDB, IntexReptServBLClient _proxy, string userID = "        ")
            : base(_OracleDB, _proxy, userID)
        {
            SetExt = new DataSet();
            INTEXDBC = new INTEXDBC(_OracleDB, _proxy);
            dbcon = "Data Source=|DataDirectory|C:\\IntexReport\\MINTEX\\PhoneDB\\MINTEXDB\\mintexdb.sdf";
        }
        public SDTDBSYNData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public void GetData(string _dbtype = "SDF", bool SaveXML = false)
        {
            /*
            if (LiveClient == null)
                return;*/
            dbtype = _dbtype;
            PrepareData(SaveXML);//产生数据库和xml，如果xml要上传，则xml要以.ITX为后缀
            return;
            //PrepareData(_dbtype, SaveXML);//产生数据库和xml，如果xml要上传，则xml要以.ITX为后缀
            ZipFolder(root);//将PhoneDB都压缩，准备出zip.ITX文件
            UploadZip(root);//将zip.ITX上传
            UploadPictures(root);
            UpdateSkydriveFileList(root);//更新服务器上的文件列表，然后上传到服务器的根目录
        }
        public void PrepareData(bool SaveXML)
        {
            string tbstr = "";
            string rootx = root + "\\PhoneDB";
            string option = "LE";
            bool mergedb = true;
            bool SaveXml = true;

            MINTEXDB = new MINTEXDB(dbcon);

            if (!System.IO.File.Exists(MINTEXDB.Connection.DataSource))
            {
                MINTEXDB.CreateDatabase();
            }
            XDocument xdoc = null;
            DataTable tbVAKOLL = null, tbVAART = null, tbKDSTAMM = null, tbLIEFDAT = null, tbTemp = null;
            EventDispatcher.Instance.RaiseStatus("Start MobileDB", StatusEventType.StartProgress);
            #region====BASE============
            EventDispatcher.Instance.RaiseStatus("QueryVAKOLL", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryVAKOLL(rootx, null, ref tbVAKOLL, option, SaveXml);
            MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);

            EventDispatcher.Instance.RaiseStatus("QueryVAART", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryVAART(rootx, null, ref tbVAART, option, SaveXml);
            MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            EventDispatcher.Instance.RaiseStatus("QueryKDSTAMM", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryKDSTAMM(rootx, null, ref tbKDSTAMM, option, SaveXml);
            MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);

            EventDispatcher.Instance.RaiseStatus("QueryLIEFDAT", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryLIEFDAT(rootx, null, ref tbLIEFDAT, option, SaveXml);
            MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);

            if (tbVAART == null)
                return;
            #endregion
            var query = from DataRow rows in tbVAART.AsEnumerable()
                        //where rows["VAAART"].ToString().StartsWith("BD")
                        select rows;
            foreach (DataRow dwInput in query)
            {
                string mandant = dwInput["VAAMANDANT"].ToString();
                string stage = dwInput["VAAARTST"].ToString();
                string coll = dwInput["VAAKOLL"].ToString();
                string art = dwInput["VAAART"].ToString();
                string dbname = mandant.Trim() + coll.Trim() + art.Trim();
                dbcon = "Data Source=|DataDirectory|C:\\IntexReport\\MINTEX\\PhoneDB\\MINTEXDB\\s" + dbname + ".sdf";
                using (MINTEXDB SINTEXDB = new MINTEXDB(dbcon))
                {
                    if (!System.IO.File.Exists(SINTEXDB.Connection.DataSource))
                    {
                        SINTEXDB.CreateDatabase();
                    }
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    #region====ITEM============
                    EventDispatcher.Instance.RaiseStatus("QueryVAFARBEN", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryVAFARBEN(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    switch (stage)
                    {
                        case "F":
                        case "R":
                            EventDispatcher.Instance.RaiseStatus("QueryWASTAMM", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryWASTAMM(rootx, dwInput, ref tbTemp, option, SaveXml);
                            SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            EventDispatcher.Instance.RaiseStatus("QueryWAFARBEN", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryWAFARBEN(rootx, dwInput, ref tbTemp, option, SaveXml);
                            SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            break;
                        case "G":
                            EventDispatcher.Instance.RaiseStatus("QueryGSDAT", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryGSDAT(rootx, dwInput, ref tbTemp, option, SaveXml);
                            SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            EventDispatcher.Instance.RaiseStatus("QueryGSCOLOR", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryGSCOLOR(rootx, dwInput, ref tbTemp, option, SaveXml);
                            SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            break;
                        default:
                            break;
                    }

                    #endregion
                    #region====SALES============
                    EventDispatcher.Instance.RaiseStatus("QueryVKKOPF", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryVKKOPF(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    EventDispatcher.Instance.RaiseStatus("QueryVKPOS", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryVKPOS(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    #endregion
                    #region====SHIPPING============
                    #endregion
                    #region====INVENTORY============
                    EventDispatcher.Instance.RaiseStatus("QueryWBDAT", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryWBDAT(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);

                    #endregion
                    #region====PRODUCTION============
                    EventDispatcher.Instance.RaiseStatus("QueryABPOS", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryABPOS(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    /*
                        EventDispatcher.Instance.RaiseStatus("QueryGPBST", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryGPBST(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);    
                    EventDispatcher.Instance.RaiseStatus("QueryWDECK", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryWDECK(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                 
                    EventDispatcher.Instance.RaiseStatus("QueryABAUFM", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryABAUFM(root, option, SaveXml);
                    MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);

                EventDispatcher.Instance.RaiseStatus("QueryABFOLGE", StatusEventType.RunningProgress);
                xdoc = INTEXDBC.QueryABFOLGE(root, option, SaveXml);
                MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
          
                EventDispatcher.Instance.RaiseStatus("QueryBAAUF", StatusEventType.RunningProgress);
                xdoc = INTEXDBC.QueryBAAUF(root, option, SaveXml);
                MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            
                EventDispatcher.Instance.RaiseStatus("QueryGAAUF", StatusEventType.RunningProgress);
                xdoc = INTEXDBC.QueryGAAUF(root, option, SaveXml);
                MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                  */

                    #endregion
                    #region====PURCHASING============
                    #endregion
                    if (SINTEXDB != null)
                    {
                        SINTEXDB.Dispose();
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                    }
                }
            }
            /*
            var ma = from m in MINTEXDB.VAART select m;
            foreach (VAART row in ma)
            {
                row.VAAKOLL = "SDT13";
                Tracer.Info(row.VAAART.ToString(), row.VAABEZ.ToString());
            }*/
            //MINTEXDB.SubmitChanges();
        }
        public void PrepareData(string _dbtype, bool SaveXML)
        {
            string tbstr = "";
            string rootx = root + "\\PhoneDB";
            string option = "LE";
            bool mergedb = true;
            bool SaveXml = true;
            string version = ";Version=3;";
            string pre = "Data Source=";
            string fsource = @"C:\IntexReport\MINTEX\PhoneDB\mIntexDB.sqlite";
            if (dbtype == "SQLite")
            {
                dbcon = @"C:\IntexReport\MINTEX\PhoneDB\MINTEXDB\mintexdb.sqlite";
            }
            if (!System.IO.File.Exists(dbcon))
            {
                System.IO.File.Copy(fsource, dbcon);
            }
            SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(pre + dbcon + version);
            conn.Open();

            XDocument xdoc = null;
            DataTable tbVAKOLL = null, tbVAART = null, tbKDSTAMM = null, tbLIEFDAT = null, tbTemp = null;
            EventDispatcher.Instance.RaiseStatus("Start MobileDB", StatusEventType.StartProgress);
            #region====BASE============
            EventDispatcher.Instance.RaiseStatus("QueryVAKOLL", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryVAKOLL(rootx, null, ref tbVAKOLL, option, SaveXml);
            //MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            MergeTable(conn, tbVAKOLL);

            EventDispatcher.Instance.RaiseStatus("QueryVAART", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryVAART(rootx, null, ref tbVAART, option, SaveXml);
            //MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            MergeTable(conn, tbVAART);
            EventDispatcher.Instance.RaiseStatus("QueryKDSTAMM", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryKDSTAMM(rootx, null, ref tbKDSTAMM, option, SaveXml);
            //MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            MergeTable(conn, tbKDSTAMM);

            EventDispatcher.Instance.RaiseStatus("QueryLIEFDAT", StatusEventType.RunningProgress);
            xdoc = INTEXDBC.QueryLIEFDAT(rootx, null, ref tbLIEFDAT, option, SaveXml);
            //MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            MergeTable(conn, tbLIEFDAT);             
            #endregion
            foreach (DataRow dwInput in tbVAART.Rows)
            {
                string mandant = dwInput["VAAMANDANT"].ToString();
                string stage = dwInput["VAAARTST"].ToString();
                string coll = dwInput["VAAKOLL"].ToString();
                string art = dwInput["VAAART"].ToString();
                string dbname = mandant.Trim() + coll.Trim() + art.Trim();
                dbcon = "C:\\IntexReport\\MINTEX\\PhoneDB\\MINTEXDB\\s" + dbname + ".sqlite";
                if (!System.IO.File.Exists(dbcon))
                {
                    System.IO.File.Copy(fsource, dbcon);
                }
                using (SQLiteConnection sdb = new SQLiteConnection(pre + dbcon + version))
                { 
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    #region====ITEM============
                    EventDispatcher.Instance.RaiseStatus("QueryVAFARBEN", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryVAFARBEN(rootx, dwInput, ref tbTemp, option, SaveXml);
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    MergeTable(sdb, tbTemp);
                    switch (stage)
                    {
                        case "F":
                        case "R":
                            EventDispatcher.Instance.RaiseStatus("QueryWASTAMM", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryWASTAMM(rootx, dwInput, ref tbTemp, option, SaveXml);
//                            SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            MergeTable(sdb, tbTemp);
                            EventDispatcher.Instance.RaiseStatus("QueryWAFARBEN", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryWAFARBEN(rootx, dwInput, ref tbTemp, option, SaveXml);
                            //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            MergeTable(sdb, tbTemp);
                            break;
                        case "G":
                            EventDispatcher.Instance.RaiseStatus("QueryGSDAT", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryGSDAT(rootx, dwInput, ref tbTemp, option, SaveXml);
                            //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            MergeTable(sdb, tbTemp);
                            EventDispatcher.Instance.RaiseStatus("QueryGSCOLOR", StatusEventType.RunningProgress);
                            xdoc = INTEXDBC.QueryGSCOLOR(rootx, dwInput, ref tbTemp, option, SaveXml);
                            //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                            MergeTable(sdb, tbTemp);
                            break;
                        default:
                            break;
                    }

                    #endregion
                    #region====SALES============
                    EventDispatcher.Instance.RaiseStatus("QueryVKKOPF", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryVKKOPF(rootx, dwInput, ref tbTemp, option, SaveXml);
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    MergeTable(sdb, tbTemp);
                    EventDispatcher.Instance.RaiseStatus("QueryVKPOS", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryVKPOS(rootx, dwInput, ref tbTemp, option, SaveXml);
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    MergeTable(sdb, tbTemp);
                    #endregion
                    #region====SHIPPING============
                    #endregion
                    #region====INVENTORY============
                    EventDispatcher.Instance.RaiseStatus("QueryWBDAT", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryWBDAT(rootx, dwInput, ref tbTemp, option, SaveXml);
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    MergeTable(sdb, tbTemp);

                    #endregion
                    #region====PRODUCTION============
                    EventDispatcher.Instance.RaiseStatus("QueryABPOS", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryABPOS(rootx, dwInput, ref tbTemp, option, SaveXml);
                    //SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                    MergeTable(sdb, tbTemp);
                    /*
                        EventDispatcher.Instance.RaiseStatus("QueryGPBST", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryGPBST(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);    
                    EventDispatcher.Instance.RaiseStatus("QueryWDECK", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryWDECK(rootx, dwInput, ref tbTemp, option, SaveXml);
                    SINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                 
                    EventDispatcher.Instance.RaiseStatus("QueryABAUFM", StatusEventType.RunningProgress);
                    xdoc = INTEXDBC.QueryABAUFM(root, option, SaveXml);
                    MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);

                EventDispatcher.Instance.RaiseStatus("QueryABFOLGE", StatusEventType.RunningProgress);
                xdoc = INTEXDBC.QueryABFOLGE(root, option, SaveXml);
                MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
          
                EventDispatcher.Instance.RaiseStatus("QueryBAAUF", StatusEventType.RunningProgress);
                xdoc = INTEXDBC.QueryBAAUF(root, option, SaveXml);
                MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
            
                EventDispatcher.Instance.RaiseStatus("QueryGAAUF", StatusEventType.RunningProgress);
                xdoc = INTEXDBC.QueryGAAUF(root, option, SaveXml);
                MINTEXDB.DBMapper.MapFinal(xdoc, mergedb);
                  */

                    #endregion
                    #region====PURCHASING============
                    #endregion
                    if (sdb != null)
                    {
                        sdb.Dispose();
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                    }
                }
            }

        }
        private void MergeTable(SQLiteConnection db, DataTable tb)
        {
            string tn = tb.TableName;
            if (db.State != ConnectionState.Open)
            {
                db.Open();
            }
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteTransaction myTrans;
            // Start a local transaction
            myTrans = db.BeginTransaction(System.Data.IsolationLevel.Serializable);
            // Assign transaction object for a pending local transaction
            cmd.Connection = db;
            cmd.Transaction = myTrans;
            string cmdt = "INSERT INTO " + tn + " ( ";
            string cms = "";
            string values = "";
            foreach (DataColumn dc in tb.Columns)
            {
                string cm = dc.ColumnName;
                cms += cm + ",";
                values += "@" + cm + ",";
                cmd.Parameters.Add(new SQLiteParameter("@" + cm, cm));
            }
            cms += "***";
            cms = cms.Replace(",***", "");
            values += "***";
            values = values.Replace(",***", "");
            cmdt = "INSERT INTO " + tn + " ( " + cms + ") VALUES (" + values + ")";
            try
            {

                cmd.CommandText = cmdt;
                foreach (DataRow rw in tb.Rows)
                {
                    foreach (DataColumn dc in tb.Columns)
                    {
                        string cm = dc.ColumnName;
                        cmd.Parameters["@" + cm].Value = rw[cm];
                    }
                    cmd.ExecuteNonQuery();
                }                
                myTrans.Commit();
            }
            catch (Exception e)
            {
                myTrans.Rollback();

            }
            finally
            {
                db.Close();
            } 
        }

    }
}